from datetime import datetime
from string import Template
from tack.lib import string_app, json_app, content_headers, status_codes
from tack.lib.smtptools import message, send_message
from util import build_empty_row_dict, build_row_dict_for_sql, fill_dict_from_form, build_columns_values_sql, state_def


class Sql:
    insert_commitment_id = """
    INSERT INTO tmpo_commitment
    DEFAULT VALUES
    """


    select_commitment_detail_columns = """
    SELECT * 
    FROM tmpo_commitment_detail
    WHERE 1 = 0
    """


    insert_commitment_details = """
    INSERT INTO tmpo_commitment_detail
    """


    get_notify_message = """
    SELECT subject,
           markup 
    FROM message_template
    WHERE keyword = 'tmpo_appt_conf'
    """


def insert_requested(environ, start_response):
    return insert(environ, start_response, state_def.REQUESTED)


def insert_resourced(environ, start_response):
    return insert(environ, start_response, state_def.RESOURCED)


def insert(environ, start_response, state):
    form = environ['form']
    throwaway = datetime.now()
    time_stamp = throwaway.strftime('%Y-%m-%d %H:%M:%S')
    sql = Template(Sql.insert_commitment_id).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    environ['connection'].commit()
    cursor.execute('SELECT CAST(@@IDENTITY AS INT)')
    rows = cursor.fetchall()
    commitment_id = rows[0][0]
    cursor.execute(Sql.select_commitment_detail_columns)
    rows = cursor.fetchall()
    state_dict = build_empty_row_dict(cursor.description)
    state_dict = fill_dict_from_form(state_dict, cursor.description, form)       
    state_dict['state_start'] = "'" + time_stamp + "'"
    del state_dict['state_finish'], state_dict['commitment_detail_id']
    state_dict['state_key'] = state
    state_dict['commitment_id'] = commitment_id
    sql = Sql.insert_commitment_details
    sql = sql + build_columns_values_sql(state_dict)
    cursor.execute(sql)
    environ['connection'].commit()
    return json_app(commitment_id)(environ, start_response)

